﻿using System;
using System.Collections.Generic;
using System.Text;
using SAServices.Model;
using System.Data;

namespace SAServices.DAL
{
    class VendorDAL
    {
        internal static bool AddVendor(Model.ConnectionConext connCtx, Model.VendorInfo newVendorInfo)
        {
            string sql = String.Format("INSERT INTO Vendors(Name, Description) Values({0}, {1});",
                         "'" + newVendorInfo.Name + "'",
                          "'" + newVendorInfo.Description + "'");

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static bool UpdateVendor(Model.ConnectionConext connCtx, Model.VendorInfo updateVendorInfo)
        {
            string sql = String.Format("UPDATE Vendors SET Name = {0}, Description = {1} WHERE ID = {2};",
                        "'" + updateVendorInfo.Name + "'",
                        "'" + updateVendorInfo.Description + "'",
                        updateVendorInfo.VendorId);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static bool DeleteVendor(Model.ConnectionConext connCtx, string vendorName)
        {
            string sql = String.Format("DELETE FROM Vendors WHERE Name = '{0}'", vendorName);

            return GenDAL.ExecuteNonQuery(connCtx, sql);
        }

        internal static Model.VendorInfo GetVendor(Model.ConnectionConext connCtx, string vendorName)
        {
            VendorInfo retVendorInfo = new VendorInfo();

            string sql = String.Format("SELECT TOP 1 * FROM Vendors WHERE Name = '{0}'", vendorName);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                retVendorInfo.VendorId = Convert.ToInt32(dt.Rows[0]["ID"]);
                retVendorInfo.Name = Convert.ToString(dt.Rows[0]["Name"]);
                retVendorInfo.Description = Convert.ToString(dt.Rows[0]["Description"]);
            }

            return retVendorInfo;
        }

        internal static List<Model.VendorInfo> GetAllVendors(Model.ConnectionConext connCtx)
        {
            List<VendorInfo> retVendorList = new List<VendorInfo>();

            string sql = String.Format("SELECT * FROM Vendors");

            DataTable dt = GenDAL.GetTable(connCtx, sql);
            for (int i = 0; i < dt.Rows.Count; i++)
			{
			    VendorInfo retVendorInfo = new VendorInfo();
                retVendorInfo.VendorId = Convert.ToInt32(dt.Rows[i]["ID"]);
                retVendorInfo.Name = Convert.ToString(dt.Rows[i]["Name"]);
                retVendorInfo.Description = Convert.ToString(dt.Rows[i]["Description"]);
                retVendorList.Add(retVendorInfo);
			}

            return retVendorList;
        }

        internal static VendorInfo GetVendor(ConnectionConext connCtx, int vendorId)
        {
            VendorInfo retVendorInfo = new VendorInfo();

            string sql = String.Format("SELECT * FROM Vendors WHERE ID = {0}", vendorId);

            DataTable dt = GenDAL.GetTable(connCtx, sql);

            if (dt.Rows.Count > 0)
            {
                retVendorInfo.VendorId = Convert.ToInt32(dt.Rows[0]["ID"]);
                retVendorInfo.Name = Convert.ToString(dt.Rows[0]["Name"]);
                retVendorInfo.Description = Convert.ToString(dt.Rows[0]["Description"]);
            }

            return retVendorInfo;
        }
    }
}
